package com.efesco.finance.gathering;

import com.bstek.dorado.data.*;
import com.bstek.dorado.data.db.SqlDataset;
import com.bstek.dorado.common.*;
import com.efesco.common.DBTools;
import com.efesco.common.Tools;
import com.efesco.system.User;

/**
 * queryInvoice_invoiceDatasetListener
 */
public class queryInvoice_invoiceDatasetListener extends AbstractDatasetListener {
  public boolean beforeLoadData(Dataset dataset)
     throws Exception {
    String fesc_user_id="",sql="",foreign_enterprise_id="";
  	
  	DoradoContext context = DoradoContext.getContext();
    Object obj = context.getAttribute(context.SESSION, "User");
    if (obj != null) {
    	User user = (User) obj;
    	int roleType = user.getRoleType();
    	if (roleType == 2)  {
    		fesc_user_id=user.getForEmployeeId();
    		foreign_enterprise_id=user.getForeignEnterpriseId();
    		sql = "select g.COMPANY_CHINESE_NAME,g.invoice_code,to_char(g.invoice_date,'yyyy-mm-dd') invoice_date,g.invoice_amount,to_char(g.arrive_date,'yyyy-mm-dd') arrive_date,g.arrive_amount,g.if_effect,g.ought_month," +
					"nvl(o.ought_amount,0) ought_amount,o.payment_version_id,nvl(g.gathering_amount,0) gathering_amount,nvl(g.rate_odds,0) rate_odds," +
					"to_char(g.verification_time,'yyyy-mm-dd') verification_time,nvl(g.if_input_detail,0) if_input_detail,g.hr_invoice_detail_id,g.pre_month,g.payment_type oType  " +
					"FROM s_ought_receive o,s_company_businessman cbm," +
					"	  (SELECT cmp.SINGLE_COMPANY_ID,cmp.COMPANY_CHINESE_NAME,i.invoice_code,i.invoice_date,i.invoice_amount,i.arrive_amount,i.arrive_date,i.if_effect,h.ought_month," +
					" 		h.gathering_amount,h.rate_odds,h.verification_time,h.if_input_detail,h.hr_invoice_detail_id,h.pre_month,h.payment_type,h.pay_send_ids " +
					"		from s_invoice i,s_hr_invoice_detail h, s_single_company cmp" +  
					"		WHERE "+ 
					"		i.invoice_id=h.invoice_id(+) and cmp.SINGLE_COMPANY_ID = i.SINGLE_COMPANY_ID AND " + 
					"		cmp.FOREIGN_ENTERPRISE_ID = " + foreign_enterprise_id  +
					" 		and cmp.SINGLE_COMPANY_ID in "+DBTools.getRightSqlByCom(fesc_user_id,"cmp")+" ) g " +
					"where o.single_company_id(+)=g.single_company_id and o.ought_month(+)=g.ought_month " +
					"and o.payment_type(+)=g.payment_type and o.pay_send_ids(+)=g.pay_send_ids " +
					"and g.single_company_id=cbm.single_company_id and cbm.business_id=1 and cbm.start_date<=sysdate " + 
					"and (cbm.end_date is null or cbm.end_date>=sysdate) " ;
    	}
    	else  {
    		sql = "select g.COMPANY_CHINESE_NAME,g.invoice_code,to_char(g.invoice_date,'yyyy-mm-dd') invoice_date,g.invoice_amount,g.if_effect,g.ought_month," +
					"nvl(o.ought_amount,0) ought_amount,o.payment_version_id,nvl(g.gathering_amount,0) gathering_amount,nvl(g.rate_odds,0) rate_odds," +
					"to_char(g.verification_time,'yyyy-mm-dd') verification_time,nvl(g.if_input_detail,0) if_input_detail,g.hr_invoice_detail_id,g.pre_month,g.payment_type oType " +
					"FROM s_ought_receive o," +
					"	  (SELECT cmp.SINGLE_COMPANY_ID,cmp.COMPANY_CHINESE_NAME,i.invoice_code,i.invoice_date,i.invoice_amount,i.if_effect,h.ought_month," +
					" 		h.gathering_amount,h.rate_odds,h.verification_time,h.if_input_detail,h.hr_invoice_detail_id,h.pre_month,h.payment_type,h.pay_send_ids " +
					"		from s_invoice i,s_hr_invoice_detail h, s_single_company cmp " +  
					"		WHERE "+ 
					"		i.invoice_id=h.invoice_id(+) and cmp.SINGLE_COMPANY_ID = i.SINGLE_COMPANY_ID ) g " +
					"where o.single_company_id(+)=g.single_company_id and o.ought_month(+)=g.ought_month " +
					"and o.payment_type(+)=g.payment_type and o.pay_send_ids(+)=g.pay_send_ids " ;
    	}
    	if (!Tools.toStr(dataset.parameters().getString("singleCompanyId")).equals("")
    		&& !Tools.toStr(dataset.parameters().getString("singleCompanyId")).equals("null")	)  {
    		sql = sql + " and g.single_company_id = " + dataset.parameters().getString("singleCompanyId");
    	}
    	if (!Tools.toStr(dataset.parameters().getString("oughtMonth1")).equals("")
        	&& !Tools.toStr(dataset.parameters().getString("oughtMonth1")).equals("null")	)  {
        	sql = sql + " and g.ought_month >= '" + dataset.parameters().getString("oughtMonth1")+"'";
        }
    	if (!Tools.toStr(dataset.parameters().getString("oughtMonth2")).equals("")
            && !Tools.toStr(dataset.parameters().getString("oughtMonth2")).equals("null")	)  {
            sql = sql + " and g.ought_month <= '" + dataset.parameters().getString("oughtMonth2")+"'";
        }
    	if (!Tools.toStr(dataset.parameters().getString("amount1")).equals("")
    		&& !Tools.toStr(dataset.parameters().getString("amount1")).equals("null")	)  {
            sql = sql + " and g.invoice_amount >= " + dataset.parameters().getString("amount1");
        }
    	if (!Tools.toStr(dataset.parameters().getString("amount2")).equals("")
        	&& !Tools.toStr(dataset.parameters().getString("amount2")).equals("null")	)  {
            sql = sql + " and g.invoice_amount <= " + dataset.parameters().getString("amount2");
        }
    	if (!Tools.toStr(dataset.parameters().getString("businessmanId")).equals("")
        	&& !Tools.toStr(dataset.parameters().getString("businessmanId")).equals("null")	)  {
        	sql = sql + " and cbm.businessman_id = " + dataset.parameters().getString("businessmanId");
        }   
    	if (!Tools.toStr(dataset.parameters().getString("date1")).equals("")
        	&& !Tools.toStr(dataset.parameters().getString("date1")).equals("null")	)  {
    		String date1=Tools.format(dataset.parameters().getDate("date1"),"yyyy-MM-dd");
            sql = sql + " and g.invoice_date >= to_date('" + date1 + "','yyyy-mm-dd')";
        }
        if (!Tools.toStr(dataset.parameters().getString("date2")).equals("")
           	&& !Tools.toStr(dataset.parameters().getString("date2")).equals("null")	)  {
        	String date2=Tools.format(dataset.parameters().getDate("date2"),"yyyy-MM-dd");
        	sql = sql + " and g.invoice_date <= to_date('" + date2 + "','yyyy-mm-dd')";
        }
        if (!Tools.toStr(dataset.parameters().getString("invoiceCode")).equals("")
        	&& !Tools.toStr(dataset.parameters().getString("invoiceCode")).equals("null")	)  {
            sql = sql + " and g.invoice_code = '" + dataset.parameters().getString("invoiceCode") + "'";
        }
        if (!Tools.toStr(dataset.parameters().getString("ifEffect")).equals("")
           	&& !Tools.toStr(dataset.parameters().getString("ifEffect")).equals("null")	)  {
            sql = sql + " and g.if_effect = " + dataset.parameters().getString("ifEffect");
        }        
    	sql = sql + " order by g.invoice_date desc,g.COMPANY_CHINESE_NAME ";
    	SqlDataset sqlDataset=(SqlDataset)dataset;
    	sqlDataset.setSql(sql);
    }
  	
    return true;
  }

}
